from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

db = SQLAlchemy()

class HierarchyConfig(db.Model):
    __tablename__ = 'hierarchy_config'
    
    node_id = db.Column(db.String(50), primary_key=True)
    parent_id = db.Column(db.String(50), db.ForeignKey('hierarchy_config.node_id'))
    level = db.Column(db.String(10), nullable=False)
    code = db.Column(db.String(50), nullable=False)
    name = db.Column(db.String(100), nullable=False)
    is_active = db.Column(db.Boolean, default=True)
    created_time = db.Column(db.DateTime, default=datetime.utcnow)
    updated_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    version = db.Column(db.String(20))
    
    children = db.relationship('HierarchyConfig', backref=db.backref('parent', remote_side=[node_id]))

class RequirementType(db.Model):
    __tablename__ = 'requirement_type'
    
    type_id = db.Column(db.String(50), primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    itil_category = db.Column(db.String(20), nullable=False)
    allowed_levels = db.Column(db.String(50), nullable=False)
    domain_code = db.Column(db.String(10), nullable=False)
    created_time = db.Column(db.DateTime, default=datetime.utcnow)
    updated_time = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class Communication(db.Model):
    __tablename__ = 'communication'
    comm_id = db.Column(db.String(20), primary_key=True)  # MTG-YYYYMMDD-001
    meeting_time = db.Column(db.DateTime, nullable=False)
    location = db.Column(db.String(100))
    department = db.Column(db.String(50))
    recorder = db.Column(db.String(50))
    proposer = db.Column(db.String(50))
    method = db.Column(db.String(20))  # 会议/邮件/电话等
    content = db.Column(db.Text, nullable=False)
    notes = db.Column(db.Text)
    related_req_id = db.Column(db.String(50), db.ForeignKey('requirement_pool.req_id'))
    l3_value_stream = db.Column(db.String(20), db.ForeignKey('hierarchy_config.node_id'))
    action_items = db.Column(db.Text)
    status = db.Column(db.String(20), default='open')  # open/completed
    
    # 定义关系
    requirement = db.relationship('RequirementPool', backref='communications')
    hierarchy = db.relationship('HierarchyConfig', backref='communications')

class RequirementClassification(db.Model):
    __tablename__ = 'requirement_classification'
    
    req_id = db.Column(db.String(50), primary_key=True)
    node_id = db.Column(db.String(50), db.ForeignKey('hierarchy_config.node_id'), nullable=False)
    type_id = db.Column(db.String(50), db.ForeignKey('requirement_type.type_id'), nullable=False)
    full_path = db.Column(db.Text)
    value_metric = db.Column(db.Text)
    impact_dimensions = db.Column(db.Text)
    created_time = db.Column(db.DateTime, default=datetime.utcnow)
    
    hierarchy = db.relationship('HierarchyConfig', backref='classifications')
    req_type = db.relationship('RequirementType', backref='classifications')

class RequirementPool(db.Model):
    __tablename__ = 'requirement_pool'
    
    req_id = db.Column(db.String(50), db.ForeignKey('requirement_classification.req_id'), primary_key=True)
    request_time = db.Column(db.DateTime)
    expected_solve_time = db.Column(db.DateTime)
    business_value = db.Column(db.Integer)
    complexity = db.Column(db.Integer)
    urgency = db.Column(db.Integer)
    priority = db.Column(db.Integer)
    status = db.Column(db.String(20))
    description = db.Column(db.Text)
    solution = db.Column(db.Text)
    solution_owner = db.Column(db.String(50))
    risk_register = db.Column(db.Text)
    acceptance_criteria = db.Column(db.Text)
    close_reason = db.Column(db.Text)
    type_code = db.Column(db.String(50))
    status_update_time = db.Column(db.DateTime)
    created_time = db.Column(db.DateTime, default=datetime.utcnow)
    
    classification = db.relationship('RequirementClassification', backref='pool', uselist=False)

# 新增图表模型
class Diagram(db.Model):
    __tablename__ = 'diagram'
    id = db.Column(db.Integer, primary_key=True)
    req_id = db.Column(db.String(50), db.ForeignKey('requirement_pool.req_id'), nullable=False)
    diagram_type = db.Column(db.String(20))  # erd/sequence/flowchart
    title = db.Column(db.String(100))
    mermaid_code = db.Column(db.Text)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关系定义
    requirement = db.relationship('RequirementPool', backref=db.backref('diagrams', lazy=True))